﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
//using System.Windows.Forms;

namespace EC_DataBase
{
    public class Person_Info
    {
        public Person_Info()
        {
            name = "";
            e_name = "";
            sex = "";
            birthday = "";
            id_card = "";
            address = "";
            phone = "";
            cellphone = "";
            email = "";
        }
        public string name;
        public string e_name;
        public string sex;
        public string birthday;
        public string id_card;
        public string address;
        public string phone;
        public string cellphone;
        public string email;
    };
    public class Cert_Info
    {
        public Cert_Info()
        {
            cert_id = "";
            pub_key = "";
            reg_time = "";
            state = "";
        }
        public string cert_id;
        public string pub_key;
        public string reg_time;
        public string state;//"True" or "False"
    }
    public class Account_Info
    {
        public Account_Info()
        {
            id = "";
            type = "";
            person = "";
            bank = "";
            reg_time = "";
            money = "";
            useable = "";
            password = "";
        }
        public string id;
        public string type;
        public string person;
        public string bank;
        public string reg_time;
        public string money;
        public string useable;
        public string password;
    }

    public static class OperateDB
    {
        public static string TN_PERSON_INFO = "PERSON_INFO";
        public static string TN_PERSON_ACCOUNT = "PERSON_ACCOUNT";
        public static string TN_ACCOUNT_INFO = "ACCOUNT_INFO";
        public static string TN_TRANS_DETAIL = "TRANS_DETAIL";
        public static string TN_CHEQUE_INFO = "CHEQUE_INFO";
        public static string TN_CHEQUE_BOOK = "CHEQUE_BOOK";
        public static string TN_CERTIFICATES = "CERTIFICATES";
        public static string TN_SAFE_DOWN = "SAFE_DOWN";


        //得到个人信息
        public static Person_Info Get_Person_Info(int id)
        {
            try
            {
                Person_Info piTemp = new Person_Info();
                DataTable dt = AccessDB.ExectueSql("select * from PERSON_INFO where ID=" + id);
                piTemp.name = dt.Rows[0][1].ToString();
                piTemp.sex = dt.Rows[0][2].ToString();
                piTemp.e_name = dt.Rows[0][3].ToString();
                piTemp.birthday = DateTime.Parse(dt.Rows[0][4].ToString()).ToString("yyyy-MM-dd");
                piTemp.id_card = dt.Rows[0][5].ToString();
                piTemp.address = dt.Rows[0][6].ToString();
                piTemp.phone = dt.Rows[0][7].ToString();
                piTemp.email = dt.Rows[0][8].ToString();
                piTemp.cellphone = dt.Rows[0][12].ToString();
                return piTemp;
            }
            catch
            {
                //MessageBox.Show("get person info error!");
                //出错返回空结构体
                return null;
            }
        }

        // 通过身份证得到UID。
        public static int GetUID_By_IDCard(string strIDCard)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select [ID] from PERSON_INFO where IDCARD='" + strIDCard + "'");
                return Convert.ToInt32(dt.Rows[0][0].ToString());
            }
            catch
            {
                return 0;
            }
        }

        //得到证书信息
        public static Cert_Info Get_Cert_Info(int id)
        {
            try
            {
                Cert_Info ciTemp = new Cert_Info();
                DataTable dt = AccessDB.ExectueSql("select * from CERTIFICATES where PID=" + id);
                ciTemp.cert_id = dt.Rows[0][0].ToString();
                ciTemp.pub_key = dt.Rows[0][1].ToString();
                ciTemp.reg_time = DateTime.Parse(dt.Rows[0][2].ToString()).ToString("yyyy-MM-dd");
                ciTemp.state = dt.Rows[0][4].ToString();
                return ciTemp;
            }
            catch
            {
                //MessageBox.Show("get person info error!");
                //出错返回空结构体
                return null;
            }
        }
        //得到账户信息
        public static Account_Info Get_Account_Info(string str_Acc_ID)
        {
            try
            {
                Account_Info aiTemp = new Account_Info();
                DataTable dt = AccessDB.ExectueSql("select * from ACCOUNT_INFO where ACCOUNT_ID='" + str_Acc_ID + "'");
                aiTemp.id = dt.Rows[0][0].ToString();
                aiTemp.bank = dt.Rows[0][1].ToString();
                aiTemp.reg_time = DateTime.Parse(dt.Rows[0][2].ToString()).ToString("yyyy-MM-dd");
                aiTemp.type = dt.Rows[0][3].ToString();
                aiTemp.useable = dt.Rows[0][5].ToString();
                aiTemp.money = dt.Rows[0][6].ToString();
                aiTemp.password = dt.Rows[0][7].ToString();
                dt = AccessDB.ExectueSql("select * from PERSON_ACCOUNT where ACCOUNT_ID='" + str_Acc_ID + "'");
                string str_Person_ID = dt.Rows[0][0].ToString();
                dt = AccessDB.ExectueSql("select * from PERSON_INFO where ID=" + str_Person_ID);
                aiTemp.person = dt.Rows[0][1].ToString();
                return aiTemp;
            }
            catch
            {
                //MessageBox.Show("get account info error!");
                //出错返回空结构体
                return null;
            }
        }
        //得到当前登录用户的所有账号
        public static string[] Get_All_Acc_IDs(int id)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select ACCOUNT_ID from PERSON_ACCOUNT where PERSON_ID=" + id);
                string[] str_arrAccIDs = new string[dt.Rows.Count];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    str_arrAccIDs[i] = dt.Rows[i][0].ToString();
                }
                return str_arrAccIDs;
            }
            catch
            {
                //MessageBox.Show("Error in Get_All_Acc_IDs");
                return null;
            }
        }
        //得到交易明细信息
        public static DataTable GetTransInfo(string strAcc_ID, string strBeg_Date, string strEnd_Date)
        {
            try
            {
                return AccessDB.ExectueSql("select * from TRANS_DETAIL where ACCOUNT='" + strAcc_ID + "'and TRANS_DATE>=#" + strBeg_Date + "# and  TRANS_DATE<=#" + strEnd_Date + "#");
            }
            catch
            {
                //MessageBox.Show("GetTransInfo error!");
                //出错返回
                return null;
            }
        }
        //得到银行卡密码
        public static string Get_AccountPwd(string accountID)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select [PAYPASSWORD] from ACCOUNT_INFO where ACCOUNT_ID = '" + accountID + "' ");
                return dt.Rows[0][0].ToString();
            }
            catch
            {
                return "";
            }
        }
        //得到登录密码
        public static string GetLoginPwd(int id)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select [PASSWORD] from PERSON_INFO where ID=" + id);
                return dt.Rows[0][0].ToString();
            }
            catch
            {
                return "";
            }
        }
        //更改登录密码
        public static bool ChangeLoginPwd(int id, string str_New_Pwd)
        {
            try
            {
                return (AccessDB.Update("PERSON_INFO", "[PASSWORD]='" + str_New_Pwd + "'", "ID=" + id) != 0);
            }
            catch
            {
                //MessageBox.Show("ChangeLoginPwd Error!");
                return false;
            }
        }


        //插入
        public static bool Insert_into_SafeDown(int User_ID, string File_Name)
        {
            try
            {
                return AccessDB.ExectueSqlForBool("insert into SAFE_DOWN(USER_ID,FILE_NAME) values(" + User_ID + ",'" + File_Name + "')");
            }
            catch
            {
                //MessageBox.Show("Error in Insert into SafeDown");
                return false;
            }
        }
        //是否存在
        public static bool Record_Is_In_SafeDown(int User_ID, string File_Name)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select * from SAFE_DOWN where USER_ID=" + User_ID + "and FILE_NAME='" + File_Name.Replace("'", "''") + "'");
                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                //MessageBox.Show("Error in Record_Is_In_SafeDown");
                return false;
            }
        }

        //判断某列值在表中是否存在
        //注意：第三个参数 value 给时要修改，若列类型是文本加‘’，日期加##,数字和货币不用修改。（如'王得希',#2010/1/1#,500）
        public static bool HasRows(string table_name, string col_name, string value)
        {
            try
            {
                DataTable dt = AccessDB.ExectueSql("select * from " + table_name + " where [" + col_name + "] = '" + value + "'");
                if (dt.Rows.Count <= 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
    }
}
